package com.soikc.kuaidiE.dao.imp;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.soikc.kuaidiE.bean.KuaiDi;
import com.soikc.kuaidiE.dao.KuaiDiDao;
import com.soikc.kuaidiE.util.DruidUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Set;

public class KuaiDiDaoImp implements KuaiDiDao {
    private static final String SQL_QUERY = "SELECT * FROM KuaiDi";
    private static final String SQL_INSERT = "INSERT INTO KuaiDi () VALUES();";

    /**
     * 获取所有快递
     *
     * @return
     */
    @Override
    public List<KuaiDi> queryList() {
        Connection connection = DruidUtil.getConnection();
        List<KuaiDi> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.query(connection, SQL_QUERY);
            list = KuaiDi.createList(resultSet);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }

    /**
     * 分页查询
     *
     * @param offset     开始位置
     * @param pageNumber 查询数量
     * @return
     */
    @Override
    public List<KuaiDi> queryList(int offset, int pageNumber) {
        String sql = SQL_QUERY + " LIMIT ?,?";
        Connection connection = DruidUtil.getConnection();
        List<KuaiDi> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql, offset, pageNumber);
            list = KuaiDi.createList(resultSet);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }

    /**
     * 添加一个快递
     *
     * @param kuaiDi
     * @return
     */
    @Override
    public boolean add(KuaiDi kuaiDi) {
        Connection connection = DruidUtil.getConnection();
        List list = new ArrayList();
        List<String> list1 = new ArrayList();
        JSONObject jsonObject = (JSONObject) JSON.toJSON(kuaiDi);
        Set<String> strings = jsonObject.keySet();
        strings.forEach(s -> {
            Object o = jsonObject.get(s);
            if (o == null) {
                return;
            }
            list1.add(s);
            list.add(o);
        });
        String[] s = new String[list.size()];
        Arrays.fill(s, "?");
        String s1 = Arrays.toString(s).replaceAll("^\\[|\\]$", "");
        final String[] s2 = {SQL_INSERT.replaceAll("\\(", "(" + s1)};
        list1.forEach(tt -> {
            s2[0] = s2[0].replaceFirst("\\?", tt);
        });
        int i = 0;
        try {
            i = DruidUtil.ppsUpdate(connection, s2[0], list);
        } catch (SQLException err) {
            err.printStackTrace();
            return false;
        } finally {
            DruidUtil.close(connection);
        }
        return i > 0;
    }

    /**
     * 修改一个快递
     *
     * @param id
     * @param kuaiDi
     * @return
     */
    @Override
    public boolean update(int id, KuaiDi kuaiDi) {
        Connection connection = DruidUtil.getConnection();
        final StringBuffer sql = new StringBuffer("UPDATE KuaiDi SET ");
        String ee = " WHERE ID=?";
        List list = new ArrayList();
        JSONObject jsonObject = (JSONObject) JSON.toJSON(kuaiDi);
        Set<String> strings = jsonObject.keySet();
        strings.forEach(s -> {
            Object o = jsonObject.get(s);
            if (o == null || s.equals("id")) {
                return;
            }
            sql.append(s + "=?,");
            list.add(o);
        });
        String s = sql.toString().replaceFirst(",$", ee);
        list.add(id);
        int i = 0;
        try {
            i = DruidUtil.ppsUpdate(connection, s, list);
        } catch (SQLException err) {
            err.printStackTrace();
            return false;
        } finally {
            DruidUtil.close(connection);
        }
        return i > 0;
    }


    /**
     * 以取件码查询
     *
     * @param code
     * @return
     */
    @Override
    public KuaiDi queryPickup_code(String code) {
        final String SQL_FIND_BY_CODE = "SELECT * FROM KuaiDi WHERE Pickup_code=?";
        Connection connection = DruidUtil.getConnection();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, SQL_FIND_BY_CODE, code);
            if (resultSet.next()) {
                return KuaiDi.create(resultSet);
            }
        } catch (SQLException err) {

        } finally {
            DruidUtil.close(connection);
        }
        return null;
    }

    /**
     * 以手机号查询
     *
     * @param phone
     * @return
     */
    @Override
    public List<KuaiDi> queryPhone(String phone) {
        final String sql = "SELECT * FROM KuaiDi WHERE phone=?";
        Connection connection = DruidUtil.getConnection();
        List<KuaiDi> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql, phone);
            return KuaiDi.createList(resultSet);
        } catch (SQLException err) {

        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }

    /**
     * 以存入人手机号查询
     *
     * @param phone
     * @return
     */
    @Override
    public List<KuaiDi> queryDepositor_phone(String phone) {
        final String sql = "SELECT * FROM KuaiDi WHERE Depositor_phone=?";
        Connection connection = DruidUtil.getConnection();
        List<KuaiDi> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql, phone);
            list = KuaiDi.createList(resultSet);
        } catch (SQLException err) {

        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }


    /**
     * 以单号查询
     *
     * @param single_number 快递单号
     * @return
     */
    @Override
    public KuaiDi querySingle_number(String single_number) {
        final String sql = "SELECT * FROM KuaiDi WHERE single_number=?";
        Connection connection = DruidUtil.getConnection();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql, single_number);
            if (resultSet.next()) {
                return KuaiDi.create(resultSet);
            }
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return null;
    }

    /**
     * 查询控制台信息
     *
     * @return
     */
    @Override
    public JSONObject queryConsole() {
        final String sql = "SELECT COUNT(ID) data1_size,\n" +
                " COUNT(TO_DAYS(take_out_time)=TO_DAYS(NOW()) OR NULL) data1_day,\n" +
                "                COUNT(STATUS=0 OR NULL) data2_size,\n" +
                "                COUNT(TO_DAYS(take_out_time)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day\n" +
                "                FROM KuaiDi;";
        Connection connection = DruidUtil.getConnection();
        ResultSet r = null;
        JSONObject data = new JSONObject();
        try {
            r = DruidUtil.query(connection, sql);
            JSONObject jsonObject = new JSONObject();
            if (r.next()) {
                jsonObject.put("data1_size", r.getString("data1_size"));
                jsonObject.put("data1_day", r.getString("data1_day"));
                jsonObject.put("data2_size", r.getString("data2_size"));
                jsonObject.put("data2_day", r.getString("data2_day"));
            }
            data.put("data", jsonObject);
            data.put("status", 0);
        } catch (SQLException throwables) {
            data.put("status", -1);
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return data;
    }

    /**
     * 以单号修改快递状态
     *
     * @param id
     * @param status
     * @return
     */
    @Override
    public boolean updateStatus(int id, int status) {
        final String sql = "UPDATE KuaiDi SET STATUS=? WHERE id=?";
        Connection connection = DruidUtil.getConnection();
        int resultSet;
        try {
            resultSet = DruidUtil.ppsUpdate(connection, sql, status, id);
        } catch (SQLException err) {
            err.printStackTrace();
            return false;
        } finally {
            DruidUtil.close(connection);
        }
        return resultSet > 0;
    }

    /**
     * 以id删除快递
     *
     * @param id
     * @return
     */
    @Override
    public boolean delID(int id) {
        final String sql = "DELETE FROM KuaiDi WHERE ID=?";
        Connection connection = DruidUtil.getConnection();
        int resultSet;
        try {
            resultSet = DruidUtil.ppsUpdate(connection, sql, id);
        } catch (SQLException err) {
            return false;
        } finally {
            DruidUtil.close(connection);
        }
        return resultSet > 0;
    }
}
